CREATE PROC [dbo].[ManageOrganizaionCreditCardSerial]
    @InvoiceId DECIMAL(19, 0),
    @SerialNo NVARCHAR(MAX) = NULL
AS
BEGIN
    --SET @InvoiceId = 87967;
    --SET @SerialNo = N'600004';
    DECLARE @StatusMessage NVARCHAR(MAX);
    DECLARE @StatusCode TINYINT = 0;
    BEGIN TRY
        BEGIN TRAN;
        /*------------------------------------------ Check Existing Invoice*/
        SET @StatusCode = 101;
        SET @StatusMessage = N' ǘ     ';
        IF NOT EXISTS (SELECT 1 FROM dbo.TmpHeadH WHERE Id_Havaleh = @InvoiceId)
        BEGIN
            RAISERROR(@StatusMessage, 16, 1);
        END;
        IF @SerialNo IS NOT NULL
           AND @SerialNo <> ''
        BEGIN
            /*------------------------------------------ Check Existing Club Info*/
            SET @StatusCode = 102;
            SET @StatusMessage = N'ǘ   Ԑ   ';

            IF ISNULL(
               (
                   SELECT TOP (1)
                          ClubId
                   FROM dbo.TmpHeadH
                   WHERE Id_Havaleh = @InvoiceId
                   ORDER BY Id_Havaleh
               ),
               0
                     ) = 0
            BEGIN
                RAISERROR(@StatusMessage, 16, 1);
            END;

            /*------------------------------------------ Check Existing Credit SerialNo*/
            SET @StatusCode = 103;
            SET @StatusMessage = N'     ';
            IF (NOT EXISTS
            (
                SELECT 1
                FROM dbo.OrganizationsCreditPaper
                WHERE SerialNumber = @SerialNo
            )
               )
            BEGIN
                RAISERROR(@StatusMessage, 16, 1);
            END;

            /*------------------------------------------ Get Credit By SerialNo*/
            DECLARE @ValidateDate DATE,
                    @Status TINYINT,
                    @Id UNIQUEIDENTIFIER,
                    @Amount MONEY,
                    @UserId SMALLINT,
                    @ClubId INT;

            SELECT @ValidateDate = CAST(ValidityDate AS DATE),
                   @Status = [Status],
                   @Id = Id
            FROM dbo.OrganizationsCreditPaper
            WHERE SerialNumber = @SerialNo;

            /*------------------------------------------ Check Validaty Credit */
            SET @StatusCode = 104;
            SET @StatusMessage = N'      ';
            IF @Status = 1
            BEGIN
                RAISERROR(@StatusMessage, 16, 1);
            END;

            SET @StatusCode = 105;
            SET @StatusMessage = N'    ǘ  ';
            IF @Status = 2
            BEGIN
                RAISERROR(@StatusMessage, 16, 1);
            END;

            SET @StatusCode = 106;
            SET @StatusMessage = N'       ';
            IF CAST(GETDATE() AS DATE) > @ValidateDate
            BEGIN
                RAISERROR(@StatusMessage, 16, 1);
            END;

            /*------------------------------------------ Get Invoice Info */
            SELECT @UserId = [User_id],
                   @ClubId = ClubId
            FROM dbo.TmpHeadH
            WHERE Id_Havaleh = @InvoiceId;

            /*------------------------------------------ Set Used Status for Inputed Serial No */
            UPDATE dbo.OrganizationsCreditPaper
            SET [Status] = 1,
                UsedOn = GETDATE(),
                ClubId = @ClubId,
                InvoiceId = @InvoiceId
            WHERE Id = @Id;

        END;
        /*------------------------------------------ Return Data */
        SET @StatusCode = 100;
        SET @StatusMessage = N'    ';

        SELECT @StatusCode StatusCode,
               @StatusMessage StatusMessage;

        SELECT Id,
               SerialNumber,
               CreditAmount
        FROM dbo.OrganizationsCreditPaper
        WHERE InvoiceId = @InvoiceId
              AND [Status] = 1;

        COMMIT TRAN;
    END TRY
    BEGIN CATCH
        SELECT @StatusCode StatusCode,
               @StatusMessage StatusMessage;
        PRINT ERROR_MESSAGE();
        ROLLBACK TRAN;
    END CATCH;
END;